package com.lovcreate.Controller;

import com.lovcreate.utils.XlsxDBToExcel;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/7/29.
 */
@WebServlet(name = "StudentToExcelServlet",urlPatterns = {"/StudentToExcelServlet"})
public class StudentToExcelServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
        // 设置响应内容类型
        response.setContentType("text/html; charset=utf-8");
        PrintWriter out = response.getWriter();

        Connection conn = (Connection) getServletContext().getAttribute("conn");
        String sql = "select * from student_info";
        Statement stmt = null;
        ResultSet rs = null;
        FileOutputStream fs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            List<Map<String,String>> list = new ArrayList<>();
            list = convertList(rs);

            XlsxDBToExcel xlsxDBToExcel = new XlsxDBToExcel();
            XSSFWorkbook workbook = xlsxDBToExcel.dbToExcel(list);

            // 创建文件输出流，准备输出电子表格
            String fileName = "F:\\student.xlsx";       // 定义一个到处文件名字的生成规则
            File file = new File(fileName);
            fs = new FileOutputStream(file);
            workbook.write(fs);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                stmt.close();
                fs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    // 将ResultSet转换成List<Map>
    private static List<Map<String,String>> convertList(ResultSet rs) throws SQLException{
        List<Map<String,String>> list = new ArrayList<>();
        ResultSetMetaData md = rs.getMetaData();
        int colNums = md.getColumnCount();
        while(rs.next()) {
            Map<String,String> rowData = new HashMap<>();
            for(int i = 1; i <= colNums; i++) {
                rowData.put(md.getColumnName(i),rs.getString(i));
            }
            list.add(rowData);
        }
        return list;
    }
}
